← Return to doc.sitecore.com

  Create the Sitecore Databases
Prev Next
Print this page (opens in new window) Print

As of release 5.1.1, Sitecore is installed using the Firebird file based databases, which do not require additional configuration after the initial installation.  If you wish to use Microsoft SQL Server to store the Sitecore databases, you must created the databases as described in this section. 

If you use Microsoft SQL Server, you should use the appropriate database settings in your web.config.  Example settings are available in the web.config.sqlserver file.

Note: You should not share the same Sitecore databases between several Sitecore IIS installations as this may lead to caching problems. You should use a separate database installation and Stager module for the purpose.

To install the Sitecore SQL Server databases manually, open the SQL Server Enterprise Manager and perform the following steps. Note, you can download the Sitecore Database Installer tool to perform these steps automatically.

  1. Connect to server instance, select the Databases folder, and use the right mouse button menu to select the New Database... menu command (as shown below).


     
  2. In the new database dialog, provide the database name scArchive and select the OK button (as shown below).


     
  3. Select the new database and use the right mouse button menu to select the Properties menu command.  
  4. Make sure that the Recovery Model for the database (in the Options tab) is set to Simple.  Close the Properties dialog.  
  5. Select the new database and use the right mouse button menu to select the Restore Database... menu command (as shown below).


     
  6. In the Restore Database dialog, select the From device radio button on the General tab.  
  7. Choose the Select Devices... button that appears when the "From device" radio button is active.  
  8. On the dialog that appears, select the Add... button.
  9. On the dialog that appears, select the "..." button associated with the Filename field.  
  10. On the file explorer dialog, locate the scArchive.bak file from the Sitecore Web Root Databases folder.  
  11. Select OK to close the file explorer dialog and each of the other open dialogs until the Restore Database dialog is displayed again.  
  12. Select the Options tab.  
  13. Check that the location of the Physical file name column matches the appropriate value for your site. 

    For example, if SQL Server is installed on your server in the following directory:

    C:\Program Files\Microsoft SQL Server

    Then you should change "c:\sqldata" to "C:\Program Files\Microsoft SQL Server\MSSQL\Data".
     
  14. Turn on the “Force restore over existing database” toggle.
  15. Select the OK button to perform the database restore. 

Repeat the above steps for each bak file in the Sitecore Web Root Databases folder.  The name of each database should match the name of the corresponding bak file. For more information about the purpose of each database, please refer to the article entitled Understanding the Sitecore Databases.

1.  Tighten MS SQL security

This section applies to DBO's who want to tighten security on the SQL server.

Instead of giving dbowner rights (full MS SQL rights) to the user on all databases, you can create roles that have nothing but execute rights on stored procedures and read/write rights on the tables.

1.1.  Add New Login

Add new Login, e.g. "johnDoe":

Add new Login 

 

Important Note: The following steps must be completed for each database.

1.2.  New user to the database

Add the new user to the database, by right-clicking Users, New database user…  

1.3.  Add new role to the database

Add a new role, e.g. 'sitecore_user', by right-clicking Roles, New database role…

Add new user (created in step 1) to the new role (created in step 2.a).

Assign users to roles

1.4.  Grant Execute Rights

Click OK, and then reopen the new role grant execute rights to all stored procedures and all user defined functions (not system objects, e.g. prefixed with dt_):

Grant execute rights to stored procedures and user defined functions 

1.5.  Add role to db_datareader and db_datawriter

To the existing roles, db_datareader and db_datawriter, add the new role:

Add new role to db_datareader and db_datawriter system roles 

1.6.  Using SQL script to create user and role

Alternatively you may run this SQL script, that creates a user “sitecore” with password “sc”, and adds the role “sitecore_user” to each database.

If you wish to create other user and other role, make appropriate changes to the script.  

Download SQL script


Prev Next